﻿using System;
using System.Collections.Generic;
using System.Text;

namespace GenerationCode.CSClasses
{
    class PROCEDURE
    {
        private Utils.Table table;
        private string projectName;
        private string coder;
        private string email;
        private string objectClass;
        private string parameter = "";
        private string getvalusinsert = "";
        private string fieldValue;
        private string fieldDisplay;
        public PROCEDURE(Utils.Table tb, string projectname, string coder, string email, string fieldValue, string fieldDisplay)
        {
            this.table = tb;
            this.projectName = projectname;
            this.coder = coder;
            this.email = email;
            this.fieldValue = fieldValue;
            this.fieldDisplay = fieldDisplay;

        }

        private string QueryGetAllLanguageofItem()
        {
            if (!table.Name.Contains(Utils.Utility.MultilanguageTableName))
                return string.Empty;
            string conditionUpdate = string.Empty;
            string paramaterStrore = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
               
                if ((col.IsPK || inKeyList(col.Name)) && col.Name.ToLower().Trim() != Utils.Utility.LanguageIDField.ToLower().Trim())
                {
                    conditionUpdate += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    if (col.Length != "null")
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " (" + col.Length + ")" + ",\r\n";
                    else
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " " + ",\r\n";
                }
            }
            conditionUpdate = conditionUpdate.Substring(0, conditionUpdate.Length - 1);
            conditionUpdate = conditionUpdate.Replace(",", " and ");
            paramaterStrore = paramaterStrore.Substring(0, paramaterStrore.LastIndexOf(','));
            string tableColumn = GetColumnTable();
            string select = string.Format(@"
        -- Author:		<{0}>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        CREATE PROCEDURE  spGetAllLanguageofItem{1}	
        {3}
            {4}
        {5}       
        AS
        BEGIN
	       Select " + tableColumn + @" from {1}
           where {2}
        END
        GO
           ", email, table.Name,conditionUpdate,'(',paramaterStrore,')');
            return select;
        }

        private string QuerySelectAll()
        {
            string tableColumn = GetColumnTable();
            string select = string.Format(@"
        -- Author:		<{0}>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        CREATE PROCEDURE  spGetAll{1}	       
        AS
        BEGIN
	       Select " + tableColumn + @" from {1}
        END
        GO
           ", email, table.Name);
            return select;
        }
        private string QuerySearchCondition()
        {
            if (table.Name.Contains(Utils.Utility.MultilanguageTableName))
            {
                return string.Empty;
            }
            string defaultExecParam = string.Empty;
            string identityColumn = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                if ((col.IsPK||inKeyList(col.Name)) && identityColumn == string.Empty)
                    identityColumn = col.Name;
                //if (col.SqlType != "image")
                //    defaultExecParam += "null,";
            }
            defaultExecParam += "' where 1=1 ',null,null,null,null\r\n";
            string tableColumnPrefix = GetColumnTable("data");
            string tableColumn = GetColumnTable();
            string searchCondition = GetConditionTable();
            string store = string.Empty;
            store += "--exec spSearch{2}ByCondition " + defaultExecParam + "\r\n";
            store += "/*Author:{0}\r\n";
            store += " Create date:{1}\r\n";
            store += " Description:search {2} by some Condition*/\r\n";
            store += "-- =============================================\r\n";
            store += "create proc  spSearch{2}\r\n";
            store += "(\r\n";
            store += "   " + searchCondition + "\r\n";
            store += ")\r\n";
            store += "AS\r\n";
            store += "BEGIN\r\n";
            store += "  Declare @MaxValue int\r\n";
            store += "  set @MaxValue=2147483647\r\n";
            store += "  Declare @startRow int\r\n";
            store += "  Declare @endRow int\r\n";
            store += "  if(@SortField is null or @SortField='')\r\n";
            store += "     set @SortField='data." + identityColumn + "'\r\n";
            store += "  if(@SortExpression is null or @SortExpression='')\r\n";
            store += "     set @SortExpression='DESC'\r\n";
            store += "  if(@PageSize is null or @PageSize<=0)\r\n";
            store += "     set @PageSize=10\r\n";
            store += "  if(@PageIndex is null or @PageIndex<1)\r\n";
            store += "     set @PageIndex=1\r\n";
            store += "  if(@PageSize<>@MaxValue)\r\n";
            store += "     begin\r\n";
            store += "       set @startRow = (@PageIndex-1) * @PageSize + 1;\r\n";
            store += "       set @endRow =  @startRow+ @PageSize-1;\r\n";
            store += "     end\r\n";
            store += "  else\r\n";
            store += "     begin\r\n";
            store += "       set @startRow = 1;\r\n";
            store += "       set @endRow =  @PageSize;\r\n";
            store += "    end\r\n";
            store += "  Declare @query nvarchar(max)\r\n";
           // store += "  set @query=''\r\n";
            bool uniCode = false;
           
            store += "  Declare @totalSql nvarchar(max)\r\n";
            store += "  set @totalSql='\r\n";
            store += "  with result\r\n";
            store += "    (Row," + tableColumn + ")\r\n";
            store += "  as(SELECT ROW_NUMBER() OVER (ORDER BY ' + @SortField +  + ' ' + @SortExpression + '  ) as Row,";
            store += tableColumnPrefix + "\r\n";
            store += "     FROM " + this.table.Name + " data \r\n";
            store += "     left join " + this.table.Name + Utils.Utility.MultilanguageTableName + " trans on(data." + identityColumn + "=trans." + identityColumn + " and trans." + Utils.Utility.LanguageIDField + "=''' + @" + Utils.Utility.LanguageIDField + " + ''')'\r\n";
            store += "  set @totalSql=@totalSql + @Condition + ')'\r\n";
            store += "  set @totalSql=@totalSql + ' SELECT *,(Select count(*) from result) as TotalRow\r\n";
            store += "  FROM result WHERE Row between ' + convert(varchar(12), @startRow) + ' AND ' + convert(varchar(12), @endRow)\r\n";
            store += "exec(@totalSql)\r\n";

            store += "END\r\n";
            store += "GO";
            store = string.Format(store, this.coder, DateTime.Now.ToString("dd/MM/yyyy"), this.table.Name);
            return store;

        }
        private bool inKeyList(string p)
        {

            foreach (object col in table.KeyList)
            {
                if (col.ToString().Trim() == p.Trim())
                    return true;

            }
            return false;
        }
        private string QuerySelecByID()
        {
            string paramaterStrore = string.Empty;
            string tableColumn = GetColumnTable();
            string conditionGet = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                if (col.IsPK || inKeyList(col.Name))
                {
                    conditionGet += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    if (col.Length != "null")
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " (" + col.Length + ")" + ",\r\n";
                    else
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " " + ",\r\n";
                }
            }
            //if (this.table.Name.Contains(Utils.Utility.MultilanguageTableName))
            //{
            //    if(Utils.Utility.ConvertType(Utils.Utility.LanguageIDFieldSQLDataType)=="string")
            //        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(Utils.Utility.LanguageIDField) + " " + Utils.Utility.LanguageIDFieldSQLDataType + "(" + Utils.Utility.LanguageIDFieldSQLDataLength+ ")" + ",\r\n";
            //    else
            //        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(Utils.Utility.LanguageIDField) + " " + Utils.Utility.LanguageIDFieldSQLDataType + " " + ",\r\n";
            //}


            paramaterStrore = paramaterStrore.Substring(0, paramaterStrore.Length - 3);
            conditionGet = conditionGet.Substring(0, conditionGet.Length - 1);
            conditionGet = conditionGet.Replace(",", " and ");
            string select = string.Format(@"
        -- Author:		<{0}>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        CREATE PROCEDURE spGet{1}  
        {2}
        {3}
        {4}      
        AS
        BEGIN	       
	        SELECT " + tableColumn + @" from {5}", email, table.Name, '(', paramaterStrore, ')', table.Name);
            select += " where " + conditionGet + @"
        END
        GO
        ";
            return select;
        }
        private string QueryInsert()
        {
            string insertParamater = string.Empty;
            string insertField = string.Empty;
            string valueInsert=string.Empty;
            List<Utils.Column> lstPK=new List<GenerationCode.Utils.Column>();
            GetParamaterStroreInsert(ref insertParamater, ref insertField,ref valueInsert,ref lstPK);
            string sql = string.Empty;
            if (this.table.Name.Contains(Utils.Utility.MultilanguageTableName))
            {
                sql=string.Format(@"
            -- Author:{0}		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spInsert{1}
            {2}
            {3}
            {4}", this.email, this.table.Name, '(', insertParamater, ')');

                sql += string.Format(@"
            AS
            begin
                Insert into {0}{1} values {2}
                if(@@ERROR<>0)--Insert Error
                    return 0;
                return 1;
            end
            GO", table.Name, insertField, valueInsert);
            }
            else
            {
                string returnTrueValue = "@@Identity";
                if (!lstPK[0].IsIdentity)
                    returnTrueValue = "1";
                sql=string.Format(@"
            -- Author:{0}		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spInsert{1}
            {2}
            {3}
            {4}", this.email, this.table.Name, '(', insertParamater, ')');

                sql += string.Format(@"
            AS
            begin
                Insert into {0}{1} values {2}
                if(@@ERROR<>0)--Insert Error
                    return 0;
                return " + returnTrueValue+ @";
            end
            GO", table.Name, insertField, valueInsert);
            }
            return sql;
        }
        private string GetFieldUpdate()
        {
            try
            {
                string result = string.Empty;
                foreach (Utils.Column col in table.Columns)
                {
                    if (!col.IsPK && !col.IsIdentity && !inKeyList(col.Name))
                        result += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name )+ ",";
                }
                result = result.Substring(0, result.Length - 1);
                return "set " + result;
            }
            catch
            {
                return string.Empty;
            }
        }
        private string QueryInsertUpdate()
        {
            if (!table.Name.Contains(Utils.Utility.MultilanguageTableName))
                return string.Empty;
            string insertParamater = string.Empty;
            string insertField = string.Empty;
            string valueInsert = string.Empty;
            List<Utils.Column> lstPK = new List<GenerationCode.Utils.Column>();
            GetParamaterStroreInsert(ref insertParamater, ref insertField, ref valueInsert,ref lstPK);

            string fieldUpdate = GetFieldUpdate();
            if (fieldUpdate != string.Empty)
            {
                string conditionUpdate = string.Empty;
                foreach (Utils.Column col in table.Columns)
                {
                    if (col.IsPK || inKeyList(col.Name))
                        conditionUpdate += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                }
                conditionUpdate = conditionUpdate.Substring(0, conditionUpdate.Length - 1);
                conditionUpdate = conditionUpdate.Replace(",", " and ");
                string tableColum = GetParamaterStroreUpdate();
                string sql = string.Format(@"
            -- Author:{0}		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spInsertUpdate{1}
            {2}
            {3}
            {4}", this.email, this.table.Name, '(', tableColum, ')');

                sql += string.Format(@"
            AS
            begin
                if exists(Select {3} from {0} where {2})
                begin
                    Update {0} {1} where {2}
                    if(@@ERROR<>0)--Update Error
                        return 0;
                    return 1;
                end
                else
                    begin
                        Insert into {0}{4} values {5}
                        if(@@ERROR<>0)--Insert Error
                            return 0;
                        return 1;   
                    end
            end
            GO", table.Name, fieldUpdate, conditionUpdate,((Utils.Column)table.Columns[0]).Name, insertField, valueInsert);
                return sql;
            }
            return string.Empty;
        }
        private string QueryUpdate()
        {
            string fieldUpdate = GetFieldUpdate();
            if (fieldUpdate != string.Empty)
            {
                string conditionUpdate = string.Empty;
                foreach (Utils.Column col in table.Columns)
                {
                    if (col.IsPK || inKeyList(col.Name))
                        conditionUpdate += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                }
                conditionUpdate = conditionUpdate.Substring(0, conditionUpdate.Length - 1);
                conditionUpdate = conditionUpdate.Replace(",", " and ");
                string tableColum = GetParamaterStroreUpdate();
                string sql = string.Format(@"
            -- Author:{0}		
            -- Create date: <Create Date,,>
            -- Description:	<Description,,>
            -- =============================================
            CREATE PROCEDURE spUpdate{1}
            {2}
            {3}
            {4}", this.email, this.table.Name, '(', tableColum, ')');

                sql += string.Format(@"
            AS
            begin
                Update {0} {1} where {2}
                if(@@ERROR<>0)--Update Error
                    return 0;
                return 1;
            end
            GO", table.Name, fieldUpdate, conditionUpdate);
                return sql;
            }
            return string.Empty;
        }
        private string QueryDelete()
        {
            if (this.table.Name.Contains(Utils.Utility.MultilanguageTableName))
                return QueryNonMultilanguageDelete();
            return QueryMultilanguageDelete();
        }
        private string QueryMultilanguageDelete()
        {
            string multilanguageTabel = this.table.Name + Utils.Utility.MultilanguageTableName;
            string paramaterStrore = string.Empty;
            string tableColumn = GetColumnTable();
            string conditionGet = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                if (col.IsPK || inKeyList(col.Name))
                {
                    conditionGet += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    if (col.Length != "null")
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " (" + col.Length + ")" + ",\r\n";
                    else
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " " + ",\r\n";
                }
            }
            paramaterStrore = paramaterStrore.Substring(0, paramaterStrore.Length - 3);
            conditionGet = conditionGet.Substring(0, conditionGet.Length - 1);
            conditionGet = conditionGet.Replace(",", " and ");
            string deleteMultilanguageTable = string.Format("DELETE from {0} ", multilanguageTabel);
             deleteMultilanguageTable += " where " + conditionGet;
             deleteMultilanguageTable += @"
            if(@@ERROR<>0)--error
                goto lblError;";      
            string select = string.Format(@"
        -- Author:		<{0}>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        
        CREATE PROCEDURE spDelete{1}  
        {2}
        {3}
        {4}      
        AS
        BEGIN tran
            " + deleteMultilanguageTable + @"
	        DELETE from {1} ", email, table.Name, '(', paramaterStrore, ')');
            select += " where " + conditionGet;
            select += @"
           if(@@ERROR<>0)--error
                goto lblError;
           goto lblOK;
        lblOK:
			commit tran;
			return 1;
		lblError:
			rollback tran;
			return 0	           
        GO
        ";
            return select;
        }
        /// <summary>
        /// ////////
        /// </summary>
        /// <returns></returns>
        private string QueryNonMultilanguageDelete()
        {
            string paramaterStrore = string.Empty;
            string tableColumn = GetColumnTable();
            string conditionGet = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                if (col.IsPK || inKeyList(col.Name))
                {
                    conditionGet += "[" + col.Name + "]=@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    if (col.Length != "null")
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " (" + col.Length + ")" + ",\r\n";
                    else
                        paramaterStrore += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + " " + ",\r\n";
                }
            }
            paramaterStrore = paramaterStrore.Substring(0, paramaterStrore.Length - 3);
            conditionGet = conditionGet.Substring(0, conditionGet.Length - 1);
            conditionGet = conditionGet.Replace(",", " and ");
            string select = string.Format(@"
        -- Author:		<{0}>
        -- Create date: <Create Date,,>
        -- Description:	<Description,,>
        -- =============================================
        
        CREATE PROCEDURE spDelete{1}  
        {2}
        {3}
        {4}      
        AS
        BEGIN 
	        DELETE from {1} ", email, table.Name, '(', paramaterStrore, ')', table.Name);
            select += " where " + conditionGet;
            select += @"
            if(@@ERROR<>0)--error
                return 0;
            return 1;
        END           
        GO
        ";
            return select;
        }
        private string GetColum()
        {
            string parameter = "";
            foreach (Utils.Column col in table.Columns)
            {
              if (col.DataType == "string")
                  parameter += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "(" + col.Length + ") \r\n,";
              else
                    parameter += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "\r\n,";              
            }
            return parameter.Substring(0, parameter.Length - 1);
        }
        private string GetParamaterStroreUpdate()
        {
            string parameter = "";
            foreach (Utils.Column col in table.Columns)
            {
                if ((col.DataType == "string" && col.SqlType != "text" && col.SqlType != "ntext") || (col.SqlType == "binary" || col.SqlType == "varbinary"))
                        parameter += "\r\n@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "(" + col.Length + "),";
                    else
                        parameter += "\r\n@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + ",";
               


            }
            return parameter.Substring(0, parameter.Length - 1);
        }
        private void GetParamaterStroreInsert(ref string parameter, ref string insertField,ref string valueInsert,ref List<Utils.Column> lstPK)
        {
            //values()
           insertField="(";
           valueInsert = "(";
            foreach (Utils.Column col in table.Columns)
            {
                if (!col.IsIdentity)
                {
                    string propertyName = Utils.Utility.ConvertToPropertyName(col.Name);
                    if ((col.DataType == "string" && col.SqlType != "text" && col.SqlType != "ntext") || (col.SqlType == "binary" || col.SqlType == "varbinary"))
                        parameter += "\r\n@" + propertyName + " " + col.SqlType + "(" + col.Length + "),";
                    else
                        parameter += "\r\n@" + propertyName + " " + col.SqlType + ",";
                    insertField += "[" + col.Name + "],";
                    valueInsert += "@" + propertyName + ",";
                }
                if (col.IsPK || inKeyList(col.Name))
                {
                    lstPK.Add(col);
                }
            }
            parameter =parameter.Substring(0, parameter.Length - 1);
            insertField = insertField.Substring(0, insertField.Length - 1);
            valueInsert = valueInsert.Substring(0, valueInsert.Length - 1);
            insertField += ")";
            valueInsert += ")";
        }
        private string SetValueInsert()
        {

            foreach (Utils.Column col in table.Columns)
            {
                if (!col.IsPK && !inKeyList(col.Name))
                {
                    if (col.DataType == "string")
                    {
                        parameter += "\r\n@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    }
                    else
                    {
                        parameter += "\r\n@" + Utils.Utility.ConvertToPropertyName(col.Name) + ",";
                    }
                    getvalusinsert += Utils.Utility.ConvertToPropertyName(col.Name) + "\r\n,";
                }

            }
            parameter = parameter.Substring(0, parameter.Length - 1);
            getvalusinsert = getvalusinsert.Substring(0, getvalusinsert.Length - 1);
            return "";
        }
        private string GetColumUpdate()
        {
            parameter = "";
            getvalusinsert = "";
            int i = 0;
            foreach (Utils.Column col in table.Columns)
            {

                if (i != 0)
                {
                    string s = col.DataType;
                    if (s == "string")
                        parameter += Utils.Utility.ConvertToPropertyName(col.Name) + "= @" + Utils.Utility.ConvertToPropertyName(col.Name) + "\r\n,";
                    else
                        parameter += Utils.Utility.ConvertToPropertyName(col.Name) + "= @" + Utils.Utility.ConvertToPropertyName(col.Name) + "\r\n,";

                }
                else
                {
                    string s = col.DataType;
                    if (s == "string")
                        getvalusinsert += Utils.Utility.ConvertToPropertyName(col.Name) + "= @" + Utils.Utility.ConvertToPropertyName(col.Name);
                    else
                        getvalusinsert += Utils.Utility.ConvertToPropertyName(col.Name) + " = @" + Utils.Utility.ConvertToPropertyName(col.Name);
                }
                i = i + 1;

            }
            parameter = parameter.Substring(0, parameter.Length - 1);
            return "";
        }
        private string QuerySelectObjectToControl()
        {
            
            string defaultExecParam = string.Empty;            
            foreach (Utils.Column col in table.Columns)
            {
                if(col.SqlType!="image")
                    defaultExecParam += "null,";
            }
            defaultExecParam += "null,null\r\n";
            string tableColumn = string.Empty;
            if (this.fieldValue == this.fieldDisplay)
                tableColumn = this.fieldValue;
            else
                tableColumn = this.fieldValue + "," + this.fieldDisplay;
            string searchCondition = GetToControlConditionTable();
            string store = string.Empty;
            store += "\r\n--exec spGet{2}ToControl " + defaultExecParam + "\r\n";
            store += "/*Author:{0}\r\n";
            store += " Create date:{1}\r\n";
            store += " Description:get {2} by some Condition to Control*/\r\n";
            store += "-- =============================================\r\n";
            store += "create proc  spGet{2}ToControl\r\n";
            store += "(\r\n";
            store += "   " + searchCondition + "\r\n";
            store += ")\r\n";
            store += "AS\r\n";
            store += "BEGIN\r\n";           
            store += "  if(@SortField is null)\r\n";
            store += "     set @SortField='" + this.fieldDisplay + "'\r\n";
            store += "  if(@SortExpression is null)\r\n";
            store += "     set @SortExpression='DESC'\r\n";            
            store += "  Declare @query nvarchar(max)\r\n";
            store += "  set @query=''\r\n";
            bool uniCode=false;
            foreach (Utils.Column col in table.Columns)
            {
                uniCode=col.IsUnicodeFont;
                if (col.SqlType != "image")
                {
                    store += "if(@" + col.Name + " is not null)\r\n";
                    if (col.SqlType == "text" || col.SqlType == "ntext")
                    {
                        if(uniCode)
                            store += "  set @query =@query + ' and [" + col.Name + "] like N''%' + cast(@" + Utils.Utility.ConvertToPropertyName(col.Name) + " as varchar(max))  + '%'''\r\n";
                        else
                            store += "  set @query =@query + ' and [" + col.Name + "] like ''%' + cast(@" + Utils.Utility.ConvertToPropertyName(col.Name) + " as varchar(max))  + '%'''\r\n";
                    }
                    else if (col.SqlType == "bit")
                        store += "  set @query =@query + ' and [" + col.Name + "]=' + cast(@" + Utils.Utility.ConvertToPropertyName(col.Name) + " as varchar(5))\r\n";
                    else if (col.SqlType == "nchar" || col.SqlType == "varchar" || col.SqlType == "nvarchar" || col.SqlType == "char")
                    {
                        if (!uniCode)
                            store += "  set @query =@query + ' and [" + col.Name + "] like ''%' + @" + Utils.Utility.ConvertToPropertyName(col.Name) + "  + '%'''\r\n";
                        else
                            store += "  set @query =@query + ' and [" + col.Name + "] like N''%' + @" + Utils.Utility.ConvertToPropertyName(col.Name) + "  + '%'''\r\n";
                    }
                    else if (col.SqlType != "uniqueidentifier" || col.SqlType == "varbinary" || col.SqlType == "binary")
                        store += "  set @query =@query + ' and [" + col.Name + "]=' + cast(@" + Utils.Utility.ConvertToPropertyName(col.Name) + " as varchar(40))\r\n";
                    else store += "  set @query =@query + ' and [" + col.Name + "]=''' + cast(@" + Utils.Utility.ConvertToPropertyName(col.Name) + " as varchar(40)) + ''''\r\n";
                  
                }
            }
            store += "  Declare @totalSql nvarchar(max)\r\n";
            store += "  set @totalSql='\r\n";
            store += "    Select " + tableColumn + "\r\n";            
            store += "     FROM " + this.table.Name + " WHERE 1=1 '\r\n";
            store += "  set @totalSql=@totalSql + @query + 'ORDER BY ' + @SortField +  + ' ' + @SortExpression\r\n";
            store += "exec(@totalSql)\r\n";
            store += "END\r\n";
            store += "GO\r\n";
            store = string.Format(store, this.coder, DateTime.Now.ToString("dd/MM/yyyy"), this.table.Name);
            return store;
        }
        public string GetColumnTable()
        {
            string result = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                result += "[" + col.Name + "],";
            }
            if (result != string.Empty)
                result = result.Substring(0, result.Length - 1);
            return result;
        }
        public string GetColumnTable(string prefix)
        {
            string result = string.Empty;
            foreach (Utils.Column col in table.Columns)
            {
                result += prefix + ".[" + col.Name + "],";
            }
            if (result != string.Empty)
                result = result.Substring(0, result.Length - 1);
            return result;
        }
        public string GetConditionTable()
        {
            string result = string.Empty;
            result = "--Search condtiton\r\n";
           /* foreach (Utils.Column col in table.Columns)
            {
                if (col.SqlType == "nchar" || col.SqlType == "varchar" || col.SqlType == "nvarchar" || col.SqlType == "char" || col.SqlType == "varbinary" || col.SqlType == "binary")
                    result += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "(" + col.Length + ")=null,\r\n";
                else if(col.SqlType!="image")
                    result += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "=null,\r\n";
            }*/
            result += "@Condition nvarchar(max)=' where 1=1',\r\n";
            result += "--sort infor\r\n";
            result += "@SortField nvarchar(50)=null,\r\n";
            result += "@SortExpression nvarchar(4)=null,\r\n";
            result += "--Paging infor	\r\n";
            result += "@PageIndex int=1,\r\n";
            result += "@PageSize int=10,\r\n";
            result += "@" + Utils.Utility.LanguageIDField + " varchar(" + Utils.Utility.LanguageIDFieldSQLDataLength + ")='vi-VN'\r\n";
            return result;
        }
        public string GetToControlConditionTable()
        {
            string result = string.Empty;
            result = "--Search condtiton\r\n";
            foreach (Utils.Column col in table.Columns)
            {
                if (col.SqlType == "nchar" || col.SqlType == "varchar" || col.SqlType == "nvarchar" || col.SqlType == "char" || col.SqlType == "varbinary" || col.SqlType == "binary")
                    result += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "(" + col.Length + ")=null,\r\n";
                else  if(col.SqlType!="image")
                    result += "@" + Utils.Utility.ConvertToPropertyName(col.Name) + " " + col.SqlType + "=null,\r\n";
            }
            result += "--sort infor\r\n";
            result += "@SortField nvarchar(50)=null,\r\n";
            result += "@SortExpression nvarchar(4)=null\r\n";        
            return result;
        }
        public string ObjectClass
        {
            get
            {
                if (this.fieldValue != string.Empty && this.fieldDisplay != string.Empty)
                {
                    this.objectClass = this.QuerySelectAll() + this.QuerySelecByID() + this.QueryInsert() + this.QueryUpdate() + this.QueryInsertUpdate() + this.QueryGetAllLanguageofItem() +  this.QueryDelete() + this.QuerySelectObjectToControl() + this.QuerySearchCondition();
                }
                else
                {
                    this.objectClass = this.QuerySelectAll() + this.QuerySelecByID() + this.QueryInsert() + this.QueryUpdate()+ this.QueryInsertUpdate() + this.QueryGetAllLanguageofItem() + this.QueryDelete() + this.QuerySearchCondition();
                }
                return this.objectClass;
            }
        }
    }
}
